from typing import Union
from sqlalchemy import update, CursorResult, func, text, outerjoin
from sqlmodel import select, desc, col, asc
from sqlmodel.sql._expression_select_cls import Select, SelectOfScalar

from src.base.beans import PageParam, PageResult
from src.config.db import mysqlClient
from src.domain.sys_dept import SysDept
from src.domain.sys_post import SysPost
from src.domain.sys_user import SysUser, SysUserReq, SysUserResp


def get_user_by_id(user_id: int) -> SysUser:
    return mysqlClient.exec(select(SysUser).where(SysUser.user_id == user_id).where(SysUser.del_flag == False)).first()


def get_user_by_user_name(user_name: str) -> SysUser:
    user = mysqlClient.exec(
        select(SysUser).where(SysUser.user_name == user_name).where(SysUser.del_flag == False)).first()
    return user


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysUser = None):
    stmt.where(SysUser.del_flag == False)
    if entity:
        if entity.user_id:
            stmt = stmt.where(SysUser.user_id == entity.user_id)
        if entity.user_name:
            stmt = stmt.where(SysUser.user_name == entity.user_name)
        if entity.nick_name:
            stmt = stmt.where(SysUser.nick_name == entity.nick_name)
        if entity.email:
            stmt = stmt.where(SysUser.email == entity.email)
        if entity.phone:
            stmt = stmt.where(SysUser.phone == entity.phone)
        if entity.sex:
            stmt = stmt.where(SysUser.sex == entity.sex.value)
        if entity.user_type:
            stmt = stmt.where(SysUser.user_type == entity.user_type)
        if entity.status:
            stmt = stmt.where(SysUser.status == entity.status)
    return stmt


def get_user_list_by_condition(entity: SysUser = None) -> list[SysUser]:
    stmt = select(SysUser)
    stmt = where_handle(stmt, entity)
    # order_by() 是返回一个新的语句对象，而不是原地修改
    stmt = stmt.order_by(desc(SysUser.sort))
    users = mysqlClient.exec(stmt)
    return users


def get_user_page_by_condition(entity: SysUserReq = None) -> PageResult:
    search = SysUser(**entity.model_dump())
    list_stmt = (
        select(SysUser, SysDept.dept_name, SysPost.post_name)
        .outerjoin(SysDept, col(SysUser.dept_id).__eq__(SysDept.dept_id))
        .outerjoin(SysPost, col(SysUser.post_id).__eq__(SysPost.post_id))
    )
    list_stmt = where_handle(list_stmt, search)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysUser.sort))
    list_stmt = list_stmt.limit(entity.size).offset(entity.size * (entity.current - 1))
    result = mysqlClient.exec(list_stmt).all()
    users = [
        SysUserResp(**row.SysUser.dict() | {
            "dept_name": row.dept_name, "post_name": row.post_name,}) for row in result
    ]
    total_stmt = (
        select(func.count(SysUser.user_id))
        .outerjoin(SysDept, col(SysUser.dept_id).__eq__(SysDept.dept_id))
        .outerjoin(SysPost, col(SysUser.post_id).__eq__(SysPost.post_id))
    )
    total_stmt = where_handle(total_stmt, search)
    total_result = mysqlClient.exec(total_stmt)
    total = total_result.one_or_none()
    return PageResult(total=total, data=users, current=entity.current, size=entity.size)


def insert_user(entity: SysUser = None):
    mysqlClient.add(entity)


def update_user(entity: SysUser = None) -> int:
    stmt = update(SysUser).values(entity.model_dump()).where(col(SysUser.user_id).is_(entity.user_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_user(user_id: int = None) -> int:
    stmt = update(SysUser).values(SysUser(del_flag=True).model_dump()).where(col(SysUser.user_id).is_(user_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_users(user_ids: list[int] = None) -> int:
    stmt = update(SysUser).values(SysUser(del_flag=True).model_dump()).where(col(SysUser.user_id).in_(user_ids))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()
